import yaml
import sqlite3
import csv
import datetime
import calendar
import re
import threading
from threading import Thread
from time import gmtime,strftime,time,sleep,ctime,clock
from Queue import Queue

class FeatureRobot:
    
    config = yaml.load(open('config.yaml'))
    conShare = sqlite3.connect(config['development']['database_share'])
    conFeature = sqlite3.connect(config['development']['database_feature'])
    conMemory = sqlite3.connect(config['development']['database_memory'])
    
    cursorShare = conShare.cursor()
    cursorFeature = conFeature.cursor()
    cursorMemory = conMemory.cursor()
    
    today = datetime.date.today().strftime("%Y%m%d")
    start_time = 20000101
    end_time = today    
    run_start = clock()
    run_finish = clock()
    symbols = []
    dates = []
    trades = []
    period = 1800
    
    tbShare = {
        'symbols':'symbols',
        'schema':'schema_migrations',
        'urls':'urls',
        'proxys':'proxys',
        'trades':'trades'        
        }
    tbFeature = {
        'feature':'feature',
        'weight':'weight',
        'sql':'sql'
        }
    
    def __init__(self,period = 1800):
        try:            
            if True == self.checkShareDbIsExist():
                self.period = period
                self.initFeature()
                self.getPeriodDate()
                self.getPeriodSymbol()
                self.createSqlArray()
            else:
                print "************* Share Database Is Not Exist! *************"
        except Exception,e:
            print e
            
    def checkShareDbIsExist(self):
        try:
            self.cursorShare.execute( 
            """
            SELECT COUNT(*) FROM %s           
            """
            %(self.tbShare['symbols'])
            )
            num = self.cursorShare.fetchone()[0]
            if 0 != num:
                return True
            else:
                return False             
        except Exception,e:
            print e        
            
    def initFeature(self):
        try:
            """
            Init Feature Database
            """
            self.cursorFeature.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                symbol varchar(25) UNIQUE,
                name varchar(25),
                feature float            
                )
                """
                %(self.tbFeature['feature'])
                )
            
            self.cursorFeature.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                weight float,
                date varchar(25)
                )
                """
                %(self.tbFeature['weight'])
                )
            self.cursorFeature.execute(
                """
                CREATE TABLE IF NOT EXISTS %s(
                sql varchar(50),
                visited boolean
                )
                """
                %(self.tbFeature['sql'])
                )
            """            
            Commit
            """
            self.conFeature.commit()
        
        except Exception,e:
            print e
            
    def getPeriodSymbol(self):
        try:
            self.cursorShare.execute(  
                """
                SELECT symbol FROM %s GROUP BY symbol HAVING COUNT(*)>=%s
                """            
                %(self.tbShare['trades'],self.period)
                )
            symbols =  self.cursorShare.fetchall()
            for symbol in symbols:
                self.symbols.append(symbol[0])
            print "************* There Are " + str(len(self.symbols)) + " Symbols In Period. *************"
            return True
        except Exception,e:
            print e   

            
    def getPeriodDate(self):
        try:
            self.cursorShare.execute(  
                """
                SELECT date FROM %s GROUP BY date HAVING COUNT(*)>=%s
                """            
                %(self.tbShare['trades'],self.period)
                )
            dates = self.cursorShare.fetchall()
            for date in dates:
                self.dates.append(date[0])
        except Exception,e:
            print e
            
    def getSqlArrayNum(self):
        try:
            self.cursorFeature.execute(  
            """
            SELECT COUNT(*) FROM %s WHERE visited = '0'
            """            
            %(self.tbFeature['sql'])
            )
            return self.cursorFeature.fetchone()[0]
        except Exception,e:
               print e
            
    def createSqlArray(self):
        try:
            if 0 == self.getSqlArrayNum():
                arraySql = []
                for date in self.dates:
                    for symbol in self.symbols:                                
                        arraySql.append(["SELECT volume FROM " + self.tbShare['trades'] + " WHERE symbol ='" + symbol + "' AND date = '" + date +"'",0])        
                self.cursorFeature.executemany('INSERT INTO sql VALUES (?,?)',arraySql)
                self.conFeature.commit()
                print "************* New Sql Has Been Created! *************"
            else:
                pass
        except Exception,e:
            print e   
            
    def getVolumeMatrix(self):
        try:            
            pass
        except Exception,e:
            print e
         
    def run(self):
        try:            
            print "************* All Done! *************"
        except Exception,e:
            print e